﻿/********************************************************
 * 类名： InitReceiptDAL.cs
 * 功能： 收款单管理数据层
 *   ver	变更日		作者			变更内容
 * ────────────────────────────────────────
 *   V01.0	2014-02-08	李波			初版
 *
 * Copyright (c) 2013，李波版权所有
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using PSS.Entity.FundManage;
using PSS.Utility;

namespace PSS.DAL.FundManage
{
    public class InitReceiptDAL : DbHelperBase
    {
        public bool AddInitReceipt(InitReceiptEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_InitReceipt]([InitReceiptId],[ReceiptSingleNo],[CustomerId],[ReceiptSingleDate],[Remark]) ");
            strSql.Append(" VALUES(@InitReceiptId,@ReceiptSingleNo,@CustomerId,@ReceiptSingleDate,@Remark) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitReceiptId", model.InitReceiptId);
                    Db.AddInParameter(cmd, "@ReceiptSingleNo", model.ReceiptSingleNo);
                    Db.AddInParameter(cmd, "@CustomerId", model.CustomerId);
                    Db.AddInParameter(cmd, "@ReceiptSingleDate", model.ReceiptSingleDate);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public void AddInitReceiptDetail(List<InitReceiptDetailEntity> list)
        {
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapReceiptDetailId =
                    new SqlBulkCopyColumnMapping("ReceiptDetailId", "ReceiptDetailId");
                bcp.ColumnMappings.Add(mapReceiptDetailId);

                var mapInitReceiptId =
                    new SqlBulkCopyColumnMapping("InitReceiptId", "InitReceiptId");
                bcp.ColumnMappings.Add(mapInitReceiptId);

                var mapAccountId =
                    new SqlBulkCopyColumnMapping("AccountId", "AccountId");
                bcp.ColumnMappings.Add(mapAccountId);

                var mapReceivableMoney =
                    new SqlBulkCopyColumnMapping("ReceivableMoney", "ReceivableMoney");
                bcp.ColumnMappings.Add(mapReceivableMoney);

                var mapSCategoryId =
                   new SqlBulkCopyColumnMapping("SCategoryId", "SCategoryId");
                bcp.ColumnMappings.Add(mapSCategoryId);

                var mapSettleNo =
                   new SqlBulkCopyColumnMapping("SettleNo", "SettleNo");
                bcp.ColumnMappings.Add(mapSettleNo);

                var mapRemark =
                   new SqlBulkCopyColumnMapping("Remark", "Remark");
                bcp.ColumnMappings.Add(mapRemark);

                bcp.DestinationTableName = "PSS_InitReceiptDetail";

                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
        }

        public int GetTotal(InitReceiptQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM PSS_InitReceipt AS A  ");
            strSql.Append(" LEFT JOIN (SELECT InitReceiptId,SUM(ReceivableMoney) AS TotalReceivableMoney FROM PSS_InitReceiptDetail GROUP BY InitReceiptId)AS B ON A.InitReceiptId=B.InitReceiptId ");
            strSql.Append(" INNER JOIN PSS_Customer AS C ON A.CustomerId=C.CustomerId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleNo))
            {
                strSql.Append(" AND A.ReceiptSingleNo LIKE '%" + qcmodel.ReceiptSingleNo + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerId))
            {
                strSql.Append(" AND A.CustomerId=@CustomerId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateFrom))
            {
                strSql.Append(" AND A.ReceiptSingleDate >=@SReceiptSingleDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateEnd))
            {
                strSql.Append(" AND A.ReceiptSingleDate <=@EReceiptSingleDate");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.CustomerId))
                    {
                        Db.AddInParameter(cmd, "@CustomerId", DbType.Guid, new Guid(qcmodel.CustomerId));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SReceiptSingleDate", DbType.DateTime, Convert.ToDateTime(qcmodel.ReceiptSingleDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EReceiptSingleDate", DbType.DateTime, Convert.ToDateTime(qcmodel.ReceiptSingleDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<InitReceiptEntity> GetInitReceiptList(InitReceiptQcEntity qcmodel)
        {
            var list = new List<InitReceiptEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.ReceiptSingleNo desc) AS idx,A.InitReceiptId,A.ReceiptSingleDate,A.ReceiptSingleNo,C.CustomerName,B.TotalReceivableMoney,A.Remark FROM PSS_InitReceipt AS A  ");
            strSql.Append(" LEFT JOIN (SELECT InitReceiptId,SUM(ReceivableMoney) AS TotalReceivableMoney FROM PSS_InitReceiptDetail GROUP BY InitReceiptId)AS B ON A.InitReceiptId=B.InitReceiptId ");
            strSql.Append(" INNER JOIN PSS_Customer AS C ON A.CustomerId=C.CustomerId ");
            strSql.Append(" WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleNo))
            {
                strSql.Append(" AND A.ReceiptSingleNo LIKE '%" + qcmodel.ReceiptSingleNo + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.CustomerId))
            {
                strSql.Append(" AND A.CustomerId=@CustomerId ");
            }
            if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateFrom))
            {
                strSql.Append(" AND A.ReceiptSingleDate >=@SReceiptSingleDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateEnd))
            {
                strSql.Append(" AND A.ReceiptSingleDate <=@EReceiptSingleDate");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.CustomerId))
                    {
                        Db.AddInParameter(cmd, "@CustomerId", DbType.Guid, new Guid(qcmodel.CustomerId));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SReceiptSingleDate", DbType.DateTime, Convert.ToDateTime(qcmodel.ReceiptSingleDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.ReceiptSingleDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EReceiptSingleDate", DbType.DateTime, Convert.ToDateTime(qcmodel.ReceiptSingleDateEnd));
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InitReceiptEntity();
                            if (reader["InitReceiptId"] != DBNull.Value)
                                model.InitReceiptId = new Guid(reader["InitReceiptId"].ToString());
                            if (reader["ReceiptSingleDate"] != DBNull.Value)
                                model.ReceiptSingleDate = Convert.ToDateTime(reader["ReceiptSingleDate"].ToString());
                            model.ReceiptSingleNo = reader["ReceiptSingleNo"] == obj ? null : Convert.ToString(reader["ReceiptSingleNo"]);
                            model.CustomerName = reader["CustomerName"] == obj ? null : Convert.ToString(reader["CustomerName"]);
                            if (reader["TotalReceivableMoney"] != DBNull.Value)
                                model.TotalReceivableMoney = Convert.ToDecimal(reader["TotalReceivableMoney"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public InitReceiptEntity GetInitReceiptInfo(string id)
        {
            var model = new InitReceiptEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [InitReceiptId],[ReceiptSingleNo],[CustomerId],[ReceiptSingleDate],[Remark] ");
            strSql.Append(" FROM [PSS_InitReceipt] where InitReceiptId=@InitReceiptId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitReceiptId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["InitReceiptId"] != DBNull.Value)
                                model.InitReceiptId = new Guid(reader["InitReceiptId"].ToString());
                            if (reader["CustomerId"] != DBNull.Value)
                                model.CustomerId = new Guid(reader["CustomerId"].ToString());
                            if (reader["ReceiptSingleDate"] != DBNull.Value)
                                model.ReceiptSingleDate = Convert.ToDateTime(reader["ReceiptSingleDate"].ToString());
                            model.ReceiptSingleNo = reader["ReceiptSingleNo"] == obj ? null : Convert.ToString(reader["ReceiptSingleNo"]);
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public IList<InitReceiptDetailEntity> GetInitReceiptDetailList(InitReceiptDetailQcEntity qcmodel)
        {
            var list = new List<InitReceiptDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT A.ReceiptDetailId,A.InitReceiptId,A.AccountId,B.AccountCode,B.AccountName,A.ReceivableMoney,A.SCategoryId,C.SCategoryName,A.SettleNo,A.Remark FROM PSS_InitReceiptDetail AS A   ");
            strSql.Append(" LEFT JOIN PSS_Account AS B ON A.AccountId=B.AccountId  ");
            strSql.Append(" LEFT JOIN PSS_SettlementCategory AS C ON A.SCategoryId=C.SCategoryId ");
            strSql.Append(" WHERE A.[InitReceiptId]=@InitReceiptId ");
            strSql.Append(" ORDER BY A.CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitReceiptId", qcmodel.InitReceiptId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new InitReceiptDetailEntity();
                            if (reader["ReceiptDetailId"] != DBNull.Value)
                                model.ReceiptDetailId = new Guid(reader["ReceiptDetailId"].ToString());
                            if (reader["InitReceiptId"] != DBNull.Value)
                                model.InitReceiptId = new Guid(reader["InitReceiptId"].ToString());
                            if (reader["AccountId"] != DBNull.Value)
                                model.AccountId = new Guid(reader["AccountId"].ToString());
                            model.AccountCode = reader["AccountCode"] == obj ? null : Convert.ToString(reader["AccountCode"]);
                            model.AccountName = reader["AccountName"] == obj ? null : Convert.ToString(reader["AccountName"]);
                            if (reader["ReceivableMoney"] != DBNull.Value)
                                model.ReceivableMoney = Convert.ToDecimal(reader["ReceivableMoney"]);
                            if (reader["SCategoryId"] != DBNull.Value)
                                model.SCategoryId = new Guid(reader["SCategoryId"].ToString());
                            model.SCategoryName = reader["SCategoryName"] == obj ? null : Convert.ToString(reader["SCategoryName"]);
                            model.SettleNo = reader["SettleNo"] == obj ? null : Convert.ToString(reader["SettleNo"]);
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool ModifyInitReceipt(InitReceiptEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_InitReceipt] SET [ReceiptSingleNo] = @ReceiptSingleNo,[CustomerId] = @CustomerId,[ReceiptSingleDate] = @ReceiptSingleDate,[Remark] = @Remark ");
            strSql.Append(" WHERE [InitReceiptId] = @InitReceiptId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@ReceiptSingleNo", model.ReceiptSingleNo);
                    Db.AddInParameter(cmd, "@CustomerId", model.CustomerId);
                    Db.AddInParameter(cmd, "@ReceiptSingleDate", model.ReceiptSingleDate);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@InitReceiptId", model.InitReceiptId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveInitReceipt(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_InitReceipt] WHERE InitReceiptId=@InitReceiptId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitReceiptId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveInitReceiptDetail(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_InitReceiptDetail] WHERE InitReceiptId=@InitReceiptId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@InitReceiptId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
